package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.data.BranchCOADataList;
import com.mbc.rmt.entities.BranchCOA;
import com.mbc.rmt.entities.BranchCOARpt;

public class BranchCOADAOImpls extends DAO<BranchCOA> implements BranchCOADAO{
	
	public BranchCOADAOImpls(Connection conn){
		this.conn = conn;
		this.table_name = "BranchCOA";
	}

	@Override
	public void create(BranchCOA entity) throws SQLException {
		query = "insert into "+table_name+" (BranchCOAID,HostBranchID,HostBranchName,BranchID,CurrentAccountCOA," +
				"DepartmentID,ExchangegainCOA,DeptID) values ( ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getBranchCOAID());
		pstmt.setLong(i++, entity.getHostBranchID());
		pstmt.setString(i++, entity.getHostBranchName());
		pstmt.setLong(i++, entity.getBranchID());
		pstmt.setLong(i++, entity.getCurrentAccountID());
		pstmt.setLong(i++, entity.getDepartmentID());
		pstmt.setLong(i++, entity.getExchangegainCOAID());
		pstmt.setLong(i++, entity.getDeptID());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void update(BranchCOA entity) throws SQLException {
		query = "update "+table_name+" set (HostBranchID = ?, HostBranchName = ?, BranchID = ?, CurrentAccountCOA = ?, DepartmentID = ?, ExchangegainCOA = ?, DeptID = ?) " +
				"where HostBranchID = "+entity.getHostBranchID();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getHostBranchID());
		pstmt.setString(i++, entity.getHostBranchName());
		pstmt.setLong(i++, entity.getBranchID());
		pstmt.setLong(i++, entity.getCurrentAccountID());
		pstmt.setLong(i++, entity.getDepartmentID());
		pstmt.setLong(i++, entity.getExchangegainCOAID());
		pstmt.setLong(i++, entity.getDeptID());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public BranchCOADataList findBranchCOAs(CriteriaParams cri)
			throws SQLException {
		BranchCOADataList l_dataList = new BranchCOADataList();
		ArrayList<BranchCOA> l_entityList = new ArrayList<BranchCOA>();
		query ="set rowcount " + cri.getSize() +
				" select ### from (select row_number() over (order by HostBranchID)as [rowno], " +
				" bcoa.HostBranchID,bcoa.HostBranchName, " +
				" bcoa.BranchID,b.BranchCode, bcoa.CurrentAccountCOA ,coa.AC_ID CurrentAccountCOACode , " +
				" bcoa.DepartmentID,d.departmentCode DepartmentName,bcoa.ExchangegainCOA,c.AC_ID ExchangegainCOACode, " +
				" bcoa.DeptID, dd.departmentID DeptName " +
				" from BranchCOA bcoa " +
				" left join Branch b on b.BranchId = bcoa.BranchID " +
				" left join COA coa on coa.COAKey = bcoa.CurrentAccountCOA " +
				" left join Department d on d.departmentID = bcoa.DepartmentID " +
				" left join COA c on c.COAKey = bcoa.ExchangegainCOA " +
				" left join Department dd on dd.departmentID = bcoa.DeptID where 1 = 1 )" +
				table_name; 
		String l_query = query.replaceAll("###", "*") + " where rowno > " + cri.getIndex();
		
		pstmt = conn.prepareStatement(l_query);
		rs = pstmt.executeQuery();
		while (rs.next()){
			l_entityList.add(readRecord1(rs));
		}
		l_dataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		
		String l_query2 = query.replaceAll("###", "count(*)"); 
		pstmt = conn.prepareStatement(l_query2);
		rs = pstmt.executeQuery();
		
		if(rs.next()){
			l_dataList.setTotalRecord(rs.getLong(1));
		}
		
		rs.close();
		pstmt.close();
		
		return l_dataList;
	}
	
	@Override
	public BranchCOA findByBranchCOA(String code, long branchId) throws SQLException {
		BranchCOA l_BranchCOA = null;
		query = "select row_number() over(order by HostBranchID)as [rowno], " +
				" BranchCOAID,HostBranchID,HostBranchName,BranchID,CurrentAccountCOA,DepartmentID,ExchangegainCOA,DeptID " +
				" from BranchCOA " +  
				" where 1 = 1 and HostBranchName = '" + code + "' and HostBranchID = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_BranchCOA = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_BranchCOA;
	}
	
	protected BranchCOA readRecord1(ResultSet rs) throws SQLException {
		BranchCOA l_info = new BranchCOA();
		l_info.setHostBranchID(rs.getLong("HostBranchID"));
		l_info.setHostBranchName(rs.getString("HostBranchName"));
		l_info.setBranchID(rs.getLong("BranchID"));
		l_info.setBranchCode(rs.getString("BranchCode"));
		l_info.setCurrentAccountID(rs.getLong("CurrentAccountCOA"));
		l_info.setCurrentAccountCode(rs.getString("CurrentAccountCOACode"));
		l_info.setDepartmentID(rs.getLong("DepartmentID"));
		l_info.setDepartmentCode(rs.getString("DepartmentName"));
		l_info.setExchangegainCOAID(rs.getLong("ExchangegainCOA"));
		l_info.setExchangegainCOACode(rs.getString("ExchangegainCOACode"));
		l_info.setDeptID(rs.getLong("DeptID"));
		l_info.setDeptCode(rs.getString("DeptName"));
		return l_info;
	}
	
	@Override
	protected BranchCOA readRecord(ResultSet rs) throws SQLException {
		BranchCOA l_info = new BranchCOA();
		l_info.setBranchCOAID(rs.getLong("BranchCOAID"));
		l_info.setHostBranchID(rs.getLong("HostBranchID"));
		l_info.setHostBranchName(rs.getString("HostBranchName"));
		l_info.setBranchID(rs.getLong("BranchID"));
		l_info.setCurrentAccountID(rs.getLong("CurrentAccountCOA"));
		l_info.setDepartmentID(rs.getLong("DepartmentID"));
		l_info.setExchangegainCOAID(rs.getLong("ExchangegainCOA"));
		l_info.setDeptID(rs.getLong("DeptID"));
		return l_info;
	}
	
	protected BranchCOA readRecords(ResultSet rs) throws SQLException {
		BranchCOA l_info = new BranchCOA();
		l_info.setBranchCOAID(rs.getLong("BranchCOAID"));
		l_info.setHostBranchID(rs.getLong("HostBranchID"));
		l_info.setHostBranchName(rs.getString("HostBranchName"));
		l_info.setBranchID(rs.getLong("BranchID"));
		l_info.setBranchCode(rs.getString("BranchCode"));
		l_info.setCurrentAccountID(rs.getLong("CurrentAccountCOA"));
		l_info.setCurrentAccountCode(rs.getString("CurrentAccountCOACode"));
		l_info.setDepartmentID(rs.getLong("DepartmentID"));
		l_info.setDepartmentCode(rs.getString("DepartmentCode"));
		l_info.setExchangegainCOAID(rs.getLong("ExchangegainCOA"));
		l_info.setExchangegainCOACode(rs.getString("ExchangegainCOACode"));
		l_info.setDeptID(rs.getLong("DeptID"));
		l_info.setDeptCode(rs.getString("DeptCode"));
		return l_info;
	}

	@Override
	public BranchCOADataList findBranchCOAs(long branchid) throws SQLException {
		BranchCOADataList l_dataList = new BranchCOADataList();
		ArrayList<BranchCOA> l_entityList = new ArrayList<BranchCOA>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()){
			l_entityList.add(readRecord(rs));
		}
		l_dataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		return l_dataList;
	}

	@Override
	public ResultSet prepareBranchCOAReport(BranchCOARpt entity)
			throws SQLException {
		ResultSet l_rs = null;
		String l_query = "dbo.sp_cr_branchCOArpt ?, ?";
		
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		BranchCOARptParameter(l_pstmt,entity);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private void BranchCOARptParameter(CallableStatement l_pstmt,
			BranchCOARpt entity) throws SQLException{
		int i = 1;
		l_pstmt.setString(i++, entity.getTxtfromhostbranchID());
		l_pstmt.setLong(i++, entity.getBranchID());
		
	}

	@Override
	public void delete(BranchCOA entity) throws SQLException {
		query = getDeleteQuery() + " where HostBranchID = " + entity.getHostBranchID();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public ArrayList<BranchCOA> find(long key) throws SQLException {
		ArrayList<BranchCOA> l_list = new ArrayList<BranchCOA>();
		query = " select ROW_NUMBER() over(order by bcoa.BranchCOAID)No," +
				" bcoa.*,b.BranchCode,c.AC_ID CurrentAccountCOACode,d.departmentCode DepartmentCode,ec.AC_ID ExchangegainCOACode,ed.departmentCode DeptCode " +
				" from BranchCOA bcoa " + 
				" left join Branch b on b.BranchId=bcoa.BranchID " +
				" left join COA c on c.COAKey=bcoa.CurrentAccountCOA " +
				" left join Department d on d.departmentID=bcoa.DepartmentID " +
				" left join COA ec on ec.COAKey=bcoa.ExchangegainCOA " +
				" left join Department ed on ed.departmentID=bcoa.DeptID " +
				" where  bcoa.HostBranchID = " + key ;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_list.add(readRecords(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_list;
	}
	
	public int findCount(long key) throws SQLException {
		int rowcount =0;
		query = " select count(*) from BranchCOA where HostBranchID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			rowcount=rs.getInt(1);
		}
		rs.close();
		pstmt.close();
		return rowcount;
	}

	@Override
	public BranchCOA getBranchCOA(BranchCOA entity) throws SQLException {
		BranchCOA branchcoa = null;
		query = " select bcoa.*,b.BranchCode,c.AC_ID CurrentAccountCOACode,d.departmentCode DepartmentCode,ec.AC_ID ExchangegainCOACode,ed.departmentCode DeptCode " +
				" from BranchCOA bcoa " + 
				" left join Branch b on b.BranchId=bcoa.BranchID " +
				" left join COA c on c.COAKey=bcoa.CurrentAccountCOA " +
				" left join Department d on d.departmentID=bcoa.DepartmentID " +
				" left join COA ec on ec.COAKey=bcoa.ExchangegainCOA " +
				" left join Department ed on ed.departmentID=bcoa.DeptID " +
				" where  bcoa.BranchCOAID = " + entity.getBranchCOAID();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			branchcoa = readRecords(rs);
		rs.close();
		pstmt.close();
		return branchcoa;
	}

	@Override
	public BranchCOA findByHostBranchID(long defaultBranchKey, long branchKey)
			throws SQLException {
		BranchCOA branchcoa = null;
		query = " select * from BranchCOA bcoa " +
				" where bcoa.HostBranchID =  " + defaultBranchKey +
				" and bcoa.BranchID = " + branchKey ;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			branchcoa = readRecord(rs);
		rs.close();
		pstmt.close();
		return branchcoa;
	}

}
